/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package entity.connector;

import entity.bean.HouseStatistic;
import entity.bean.Show;
import entity.bean.ShowStatistic;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 *
 * @author Lusinda
 */
public class StatisticProvider {

    public static HouseStatistic getHouseStatistic(int houseNo) {
        try {
            HouseStatistic statistic = new HouseStatistic();
            Connection con = ConnectionProvider.getCon();
            // get showsInTotal
            PreparedStatement ps = con.prepareStatement("SELECT COUNT(*), AVG(price) FROM movie_show WHERE house_id = ?");
            ps.setInt(1, houseNo);
            ResultSet rs = ps.executeQuery();
            rs.next();
            statistic.setShowsInTotal(rs.getInt(1));
            statistic.setPriceInAverage(rs.getInt(2));
            // get showsInYear
            ps = con.prepareStatement("SELECT COUNT(*) FROM movie_show WHERE house_id = ? AND YEAR(movie_time) = YEAR(GETDATE())");
            ps.setInt(1, houseNo);
            rs = ps.executeQuery();
            rs.next();
            statistic.setShowsInYear(rs.getInt(1));
            // get showsInMonth
            ps = con.prepareStatement("SELECT COUNT(*) FROM movie_show WHERE house_id = ? "
                    + "AND YEAR(movie_time) = YEAR(GETDATE()) AND MONTH(movie_time) = MONTH(GETDATE())");
            ps.setInt(1, houseNo);
            rs = ps.executeQuery();
            rs.next();
            statistic.setShowsInMonth(rs.getInt(1));
            // get showsInDay
            ps = con.prepareStatement("SELECT COUNT(*) FROM movie_show WHERE house_id = ? "
                    + "AND YEAR(movie_time) = YEAR(GETDATE()) AND MONTH(movie_time) = MONTH(GETDATE()) AND DAY(movie_time) = DAY(GETDATE())");
            ps.setInt(1, houseNo);
            rs = ps.executeQuery();
            rs.next();
            statistic.setShowsInDay(rs.getInt(1));
            // get availableSeats
            ps = con.prepareStatement("SELECT * FROM house WHERE house_id = ?");
            ps.setInt(1, houseNo);
            rs = ps.executeQuery();
            rs.next();
            int total = rs.getInt(2) * rs.getInt(3);
            ps = con.prepareStatement("SELECT COUNT(*) FROM unavailable_seat WHERE house_id = ?");
            ps.setInt(1, houseNo);
            rs = ps.executeQuery();
            rs.next();
            int unavailable = rs.getInt(1);
            statistic.setAvailableSeats(total-unavailable);
            return statistic;
        } catch (Exception e) {
            return null;
        }
    }
    
    public static ShowStatistic getShowStatistic(Show show){
        try {
            ShowStatistic statistic = new ShowStatistic();
            Connection con = ConnectionProvider.getCon();
            // get ticketsInTotal and revenueInTotal
            PreparedStatement ps = con.prepareStatement("SELECT COUNT(*), SUM(price) FROM movie_ticket WHERE movie_id = ? AND movie_time = ? AND price IS NOT NULL");
            ps.setInt(1, show.getMovieID());
            ps.setString(2, TimeConvertor.calendarToString(show.getTime()));
            ResultSet rs = ps.executeQuery();
            rs.next();
            statistic.setTickets(rs.getInt(1));
            // get ticketsInYear and revenueInYear
            ps = con.prepareStatement("SELECT COUNT(*), SUM(price) FROM movie_ticket WHERE movie_id = ? AND movie_time = ? AND price IS NOT NULL");
            ps.setInt(1, show.getMovieID());
            ps.setString(2, TimeConvertor.calendarToString(show.getTime()));
            rs = ps.executeQuery();
            rs.next();
            statistic.setTickets(rs.getInt(1));
            statistic.setRevenue(rs.getInt(2));
            return statistic;
            } catch (Exception e) {
            return null;
        }
    }
}
